﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
namespace BLL
{
    /// <summary>
    /// 所以的工具方法
    /// 如地理信息的显示和相互转换
    /// </summary>
    public class ToolBLL
    {
        public static DataSet GetAllProvince()
        {
            string sqlstr = "select * from b_province";
            return DbHelperOra.Query(sqlstr);
        }
        /// <summary>
        /// 省名转换成code
        /// </summary>
        /// <param name="provinceName"></param>
        /// <returns></returns>
        public static string ProvinceToCode(string provinceName)
        {
            string sqlstr = "select * from b_province where provincename like '%" + provinceName + "%'";
            DataSet ds = DbHelperOra.Query(sqlstr);
            int rowcount = ds.Tables[0].Rows.Count;
            if (rowcount == 1)
            {
                return ds.Tables[0].Rows[0]["code"].ToString();
            }
            else
            {
                return "";//没有找到，或者找到多行 
            }
        }
        /// <summary>
        /// code转换成省名
        /// </summary>
        /// <param name="provinceName"></param>
        /// <returns></returns>
        public static string CodeToProvince(string codeName)
        {
            string sqlstr = "select * from b_province where code = '" + codeName + "'";
            DataSet ds = DbHelperOra.Query(sqlstr);
            int rowcount = ds.Tables[0].Rows.Count;
            if (rowcount == 1)
            {
                return ds.Tables[0].Rows[0]["provincename"].ToString();
            }
            else
            {
                return "";//没有找到，或者找到多行 
            }
        }

        public static DataSet GetCityByProvinceCode(string provinceCode)
        {
            string sqlstr = "select * from v_province_city where provincecode like '" + provinceCode +
                "%' ";
            return DbHelperOra.Query(sqlstr);
            
        }
        public static DataSet GetDistrictByCityCode(string cityCode)
        {
            string sqlstr = "select * from v_province_city_district where citycode like '" + cityCode +
                "%' and districtcode not like '%00'";//去掉00结尾的市
            return DbHelperOra.Query(sqlstr);
        }
        public static DataSet GetDistrictByProvinceCode(string provinceCode)
        {
            string sqlstr = "select * from v_province_city_district where provincecode like '" + provinceCode +
                "%' and districtcode not like '%00'";//去掉00结尾的市
            return DbHelperOra.Query(sqlstr);
        }

        public static bool isNumberic(string _string)
        {
            if (string.IsNullOrEmpty(_string))
                return false;
            foreach (char c in _string)
            {
                if (!char.IsDigit(c))
                    return false;
            }
            return true;
        }

        public static DataSet GetAllUserLevel()
        {
            string sqlstr = "select * from b_userlevel";
            return DbHelperOra.Query(sqlstr);
        }

        public static DataSet GetAllCategory()
        {
            string sqlstr = "select * from b_category ";
            return DbHelperOra.Query(sqlstr);
        }

        public static DataSet GetClassificationByCode(string code)
        {
            string sqlstr = "select * from b_classification where categorycode = '" + code + "'";
            return DbHelperOra.Query(sqlstr);
        }

        public static string GenerateDBLNo(string districtCode, string businessCode, string shopAttribute, string type,string organizationType)
        {
            string dblno = string.Empty;
            switch (type.ToUpper())
            {
                case "C":
                   dblno = GenerateDBLNoCompany(districtCode, businessCode); break;
                case "S":
                   dblno = GenerateDBLNoShop(districtCode,businessCode,shopAttribute);break;
                case "P":
                    dblno = GenerateDBLNoPerson(districtCode); break;
                case "O":
                    dblno = GenerateDBLNoOrganization(districtCode ,organizationType); break;
                default :
                    throw new ApplicationException("没有找到对应的多巴拉类型,type ="+type);

            }
            return dblno;
           
        }

        public static DataSet GetAllOrganizationType()
        {
            string sqlstr = "select * from B_ORGANIZATION";
            return DbHelperOra.Query(sqlstr);
        }

        public static DataSet GetAllShopAttribute()
        {
            string sqlstr = "select * from b_shopattribute";
            return DbHelperOra.Query(sqlstr);
        }

        /// <summary>
        /// 根据name在city province district表中查找结果集
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static DataSet FindLocationByName(string name)
        {
            if (name.Equals(""))
            {
                return new DataSet();
            }
            string sql = "select * from v_province_city_district_union where fullname like '%" + name + "%'";
            return  DbHelperOra.Query(sql);
        }

        public static DataSet FindLocationByCode(string code)
        {
            if (code.Equals(""))
            {
                return new DataSet();
            }
            string sql = "select * from v_province_city_district_union where code = '" + code + "'";
            return DbHelperOra.Query(sql);
        }

        private static string GenerateDBLNoPerson(string districtCode)
        {
            string last6code = GenerateDBLNoLast6Code(districtCode,"P");
            if (isNumberic(last6code))
            {
                return districtCode + last6code;
            }
            else
            {
                throw new ApplicationException("生成多巴拉码时发生错误");
            }
        }
        /// <summary>
        /// 生成公司多巴拉码
        /// </summary>
        /// <param name="districtCode">精确到区县的6位代码</param>
        /// <param name="businessCode">4位行业代码</param>
        /// <returns>14位多巴拉码</returns>
        private static string GenerateDBLNoCompany(string districtCode, string businessCode)
        {
            string last4code = GenerateDBLNoLast4Code(districtCode + businessCode,"C");
            
            if (isNumberic(last4code))
            {
                return districtCode+businessCode+ last4code;
            }
            else
            {
                throw new ApplicationException("生成多巴拉码时发生错误");
            }
        }
        /// <summary>
        /// 生成商铺的多巴拉码
        /// </summary>
        /// <param name="districtCode"></param>
        /// <param name="businessCode"></param>
        /// <param name="ShopType"></param>
        /// <returns></returns>
        private static string GenerateDBLNoShop(string districtCode,string businessCode/*4位*/,string ShopType)
        {
            string last4code = GenerateDBLNoLast4Code(districtCode + businessCode + ShopType,"S");
            if (isNumberic(last4code))
            {
                return districtCode + businessCode +ShopType+ last4code;
            }
            else
            {
                throw new ApplicationException("生成多巴拉码时发生错误");
            }
        }
        /// <summary>
        /// 生成组织的多巴拉码
        /// </summary>
        /// <param name="distinctCode"></param>
        /// <param name="organizationCode"></param>
        /// <returns></returns>
        private static string GenerateDBLNoOrganization(string distinctCode, string organizationCode)
        {
            string last3code = GenerateDBLNoLast3Code(distinctCode + organizationCode, "O");
            if (isNumberic(last3code))
            {
                return distinctCode + organizationCode + last3code;
            }
            else
            {
                throw new ApplicationException("生成多巴拉码时发生错误");
            }
        }

        private static string GenerateDBLNoLast3Code(string headCode, string businessType)
        {
            //'000000'是为了对这个地区这个分类下的多巴拉进行初次分配，这样会初次分配会从000001开始在last3code中寻找最接近的available的代码
            //出现ERROR是说明代码已经分配到999999，但是可能中间还会有空余的多巴拉
            //这可能会由于管理员开放更多的号码组合和多巴拉被注销引起
            //这样需要从头开始插空放入多巴拉
            string sqlstr = @"select nvl(min (last3code),'ERROR:这个地区的这个分类的多巴拉码已经分配到最大，需要插空分配') as minavlast3code 
                            from b_last3code where last3code > (
                            select nvl( substr(max(dblno),length(max(dblno))-2,3),'000000') as maxdblno 
                            from user_member where dbltype = '" + businessType + "' and dblno like '"
                            + headCode + "%') and availablereserve = 'A'";

            DataSet ds = DbHelperOra.Query(sqlstr);
            string last3code = ds.Tables[0].Rows[0]["minavlast3code"].ToString();
            if (last3code.StartsWith("ERROR"))
            {
                //初次分配已经分配到最后一个开放注册的号码，中间可能还会有空隙
                string sqlstr2 = @"select nvl(min(last3code),'ERROR:这个地区这个分类的多巴拉码已经全部分配完毕，请联系管理员开放更多的多巴拉代码') as minavlast3code 
                                from b_last3code t1
                                where t1.availablereserve = 'A'
                                and t1.last3code <> all(
                                select substr(dblno,length(dblno)-2,3) from user_member t2
                                where t2.dbltype = '" + businessType + "' and t2.dblno like '" + headCode + "%')";
                DataSet ds2 = DbHelperOra.Query(sqlstr2);
                if (ds2.Tables[0].Rows[0]["minavlast3code"].ToString().StartsWith("ERROR"))
                {
                    //中间已经没有空隙
                    throw new ApplicationException(headCode + "已经没有可以分配的多巴拉码，请联系管理员");
                }
                else
                {
                    return ds2.Tables[0].Rows[0]["minavlast3code"].ToString();
                }

            }
            else
            { return last3code; }
        }

        /// <summary>
        /// 生成多巴拉的后4位
        /// 给出符合规则的最近接现在已经注册的多巴拉后4位
        /// </summary>
        /// <param name="headCode">除了后4位之外的多巴拉前几位</param>
        /// <param name="businessType">搜索company时为C，搜索shop时为S</param>
        /// <returns></returns>
        private static string GenerateDBLNoLast4Code(string headCode,string businessType)
        {
            //'0000'是为了对这个地区这个分类下的多巴拉进行初次分配，这样会初次分配会从0001开始在last4code中寻找最接近的available的代码
            //出现ERROR是说明代码已经分配到9999，但是可能中间还会有空余的多巴拉
            //这可能会由于管理员开放更多的号码组合和多巴拉被注销引起
            //这样需要从头开始插空放入多巴拉
            string sqlstr = @"select nvl(min (last4code),'ERROR:这个地区的这个分类的多巴拉码已经分配到最大，需要插空分配') as minavlast4code from b_last4code where last4code > (
select nvl( substr(max(dblno),length(max(dblno))-3,4),'0000') as maxdblno from user_member where dbltype = '"+businessType+"' and dblno like '" + headCode + "%') and availablereserve = 'A'";

            DataSet ds = DbHelperOra.Query(sqlstr);
            string last4code = ds.Tables[0].Rows[0]["minavlast4code"].ToString();
            if (last4code.StartsWith("ERROR"))
            {
                //初次分配已经分配到最后一个开放注册的号码，中间可能还会有空隙
                string sqlstr2 = @"select nvl(min(last4code),'ERROR:这个地区这个分类的多巴拉码已经全部分配完毕，请联系管理员开放更多的多巴拉代码') as minavlast4code from b_last4code t1
where t1.availablereserve = 'A'
and t1.last4code <> all(
select substr(dblno,length(dblno)-3,4) from user_member t2
where  t2.dbltype = '"+businessType+"' and t2.dblno like '" + headCode + "%')";
                DataSet ds2 = DbHelperOra.Query(sqlstr2);
                if (ds2.Tables[0].Rows[0]["minavlast4code"].ToString().StartsWith("ERROR"))
                {
                    //中间已经没有空隙
                    throw new ApplicationException(headCode + "已经没有可以分配的多巴拉码，请联系管理员,type = "+businessType);
                }
                else
                {
                    return ds2.Tables[0].Rows[0]["minavlast4code"].ToString();
                }

            }
            else
            { return last4code; }
        }

        private static string GenerateDBLNoLast6Code(string headCode, string businessType)
        {
            //'000000'是为了对这个地区这个分类下的多巴拉进行初次分配，这样会初次分配会从000001开始在last6code中寻找最接近的available的代码
            //出现ERROR是说明代码已经分配到999999，但是可能中间还会有空余的多巴拉
            //这可能会由于管理员开放更多的号码组合和多巴拉被注销引起
            //这样需要从头开始插空放入多巴拉
            string sqlstr = @"select nvl(min (last6code),'ERROR:这个地区的这个分类的多巴拉码已经分配到最大，需要插空分配') as minavlast6code 
                            from b_last6code where last6code > (
                            select nvl( substr(max(dblno),length(max(dblno))-5,6),'000000') as maxdblno 
                            from user_member where dbltype = '" + businessType + "' and dblno like '" 
                            + headCode + "%') and availablereserve = 'A'";

            DataSet ds = DbHelperOra.Query(sqlstr);
            string last6code = ds.Tables[0].Rows[0]["minavlast6code"].ToString();
            if (last6code.StartsWith("ERROR"))
            {
                //初次分配已经分配到最后一个开放注册的号码，中间可能还会有空隙
                string sqlstr2 = @"select nvl(min(last6code),'ERROR:这个地区这个分类的多巴拉码已经全部分配完毕，请联系管理员开放更多的多巴拉代码') as minavlast6code 
                                from b_last6code t1
                                where t1.availablereserve = 'A'
                                and t1.last6code <> all(
                                select substr(dblno,length(dblno)-5,6) from user_member t2
                                where t2.dbltype = '" + businessType + "' and t2.dblno like '" + headCode + "%')";
                DataSet ds2 = DbHelperOra.Query(sqlstr2);
                if (ds2.Tables[0].Rows[0]["minavlast6code"].ToString().StartsWith("ERROR"))
                {
                    //中间已经没有空隙
                    throw new ApplicationException(headCode + "已经没有可以分配的多巴拉码，请联系管理员");
                }
                else
                {
                    return ds2.Tables[0].Rows[0]["minavlast6code"].ToString();
                }

            }
            else
            { return last6code; }
        }
        
        public class DBLAttribute
        {
           
            private string locationCode;

            public string LocationCode
            {
                get { return locationCode; }
                set { locationCode = value; }
            }
            private string categoryCode;

            public string CategoryCode
            {
                get { return categoryCode; }
                set { categoryCode = value; }
            }
            private string classificationCode;

            public string ClassificationCode
            {
                get { return classificationCode; }
                set { classificationCode = value; }
            }
            private string orgTypeCode;

            public string OrgTypeCode
            {
                get { return orgTypeCode; }
                set { orgTypeCode = value; }
            }
            private string shopAttribute;

            public string ShopAttribute
            {
                get { return shopAttribute; }
                set { shopAttribute = value; }
            }
            private string dblType;

            public string DblType
            {
                get { return dblType; }
                set { dblType = value; }
            }
        }
        /// <summary>
        /// 解析多巴拉吗，分析出这个码的归属地，行业，及属性
        /// </summary>
        /// <param name="dblNo"></param>
        /// <returns></returns>
        /// 
        public static DBLAttribute ParseDBLNo(string dblNo)
        {
            DBLAttribute dblAttribute = new DBLAttribute();
            int dblLength = dblNo.Trim().Length;
            switch (dblLength)
            {
                case 14://company
                    dblAttribute.DblType = "C";
                    dblAttribute.LocationCode = dblNo.Substring(0, 6);
                    dblAttribute.CategoryCode = dblNo.Substring(6, 2);
                    dblAttribute.ClassificationCode = dblNo.Substring(7,2);
                    break;
                case 16 ://SHOP
                    dblAttribute.DblType = "S";
                    dblAttribute.LocationCode = dblNo.Substring(0, 6);
                    dblAttribute.CategoryCode = dblNo.Substring(6, 2);
                    dblAttribute.ClassificationCode = dblNo.Substring(8, 2);
                    dblAttribute.ShopAttribute = dblNo.Substring(10,2);
                    break;
                case 12 :
                    dblAttribute.DblType = "P";
                    dblAttribute.LocationCode = dblNo.Substring(0, 6);
                    break;
                case 11:
                    dblAttribute.DblType = "O";
                    dblAttribute.LocationCode = dblNo.Substring(0, 6);
                    dblAttribute.OrgTypeCode = dblNo.Substring(6, 2);
                    break;
                default:
                    throw new ApplicationException("未能识别的多巴拉码的长度，多巴拉长度为"+dblLength);
                    
            }

            return dblAttribute;
        }
    }
}
